package main.orga;

import main.staff.CreateTypesSql;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class InitOrgaIdAndCodeFromExcel {
	//根节点的old_orgaId
	static String old_rootCode    = "001001006";
	//根节点的新组织机构id（根据情况自己定义）
	static String newOrgaId     = "000300040008";
	//根节点的新组织机构code（根据情况自己定义）
	static String newOrgaCode   = "001004008";
	//根节点要挂在已有的哪个组织机构下
	static String superOrgaCode = "001004";

	
	//excel中每一列的意义
	static Integer oldNameLine = 1;
	static Integer oldIdLine = 0;
	static Integer oldCodeLine = 2;
	static Integer oldSuperCodeLine = 3;
	static Integer oldDescLine = 4;
	static Integer newIdLine = 5;
	static Integer newCodeLine = 6;
	static Integer newSuperCodeLine = 7;
	
	public static Sheet sheet;
	public static FileWriter orgaInfo;
	
	public static void main(String[] args) {
		InputStream inp;
		FileOutputStream oup;
		try {
			inp = new FileInputStream("D:/河北菜单人员组织.xlsx");
			oup = new FileOutputStream(new File("D:/新河北菜单人员组织.xlsx"));
			//InputStream inp = new FileInputStream("workbook.xlsx");
			
			Workbook wb = WorkbookFactory.create(inp);
			
			
			sheet = wb.getSheetAt(2);
			orgaInfo =new FileWriter("D:/t_ucp_orgainfo.sql");
			//查询员组织机构id符合要求的excel信息
			List<Map<String, Object>> rootInfoList = queryOrgainfoByOrgaIdFromExcel(old_rootCode);
			
		
			if (rootInfoList.size() != 1) {
				System.out.println("根节点信息有误！old_rootId="+old_rootCode);
				return;
			}
			
			initOrgaInfo(old_rootCode,newOrgaId,newOrgaCode,superOrgaCode,rootInfoList.get(0));
			
			orgaInfo.write("COMMIT;\n");
			orgaInfo.close();
			
			wb.write(oup);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	private static List<Map<String,Object>>  queryOrgainfoByOrgaIdFromExcel(String old_rootCode2) {
		List<Map<String,Object>> orgaList = new ArrayList<Map<String,Object>>();
		for(int i=0;i<sheet.getLastRowNum()+1;i++){
			Map<String, Object> orgaMap = queryOrgaInfoByRownum(i);
			String old_orgaCode_temp = (String)orgaMap.get("old_orgacode");
			if (old_orgaCode_temp.equals(old_rootCode2)) {
				orgaList.add(orgaMap);
			}
		}
		return orgaList;
	}

	private static Map<String, Object> queryOrgaInfoByRownum(int i) {
		Row row = sheet.getRow(i);
		String old_orgaid_temp=CreateTypesSql.removeNull(row, oldIdLine);
		String old_organame_temp=CreateTypesSql.removeNull(row, oldNameLine);
		String old_orgacode_temp=CreateTypesSql.removeNull(row, oldCodeLine);
		String old_suoerOrgaCode_temp=CreateTypesSql.removeNull(row, oldSuperCodeLine);
		String old_orgadesc_temp=CreateTypesSql.removeNull(row, oldDescLine);
		Map<String, Object> orgaMap = new HashMap<String, Object>();
		orgaMap.put("old_orgaid", old_orgaid_temp);
		orgaMap.put("old_organame", old_organame_temp);
		orgaMap.put("old_orgacode", old_orgacode_temp);
		orgaMap.put("old_superorgacode", old_suoerOrgaCode_temp);
		orgaMap.put("old_orgadesc", old_orgadesc_temp);
		orgaMap.put("row", i);
		return orgaMap;
	}

	private static void initOrgaInfo(String old_rootId2, String newOrgaId2,
			String newOrgaCode2, String superOrgaCode2,Map<String, Object> rootMap) {
		editInsertSql( old_rootId2,  newOrgaId2, newOrgaCode2,  superOrgaCode2, rootMap);
		rootMap.get("row");
		sheet.getRow((int)rootMap.get("row")).createCell(newIdLine);
		sheet.getRow((int)rootMap.get("row")).getCell(newIdLine).setCellValue(newOrgaId2);
		sheet.getRow((int)rootMap.get("row")).createCell(newCodeLine);
		sheet.getRow((int)rootMap.get("row")).getCell(newCodeLine).setCellValue(newOrgaCode2);
		sheet.getRow((int)rootMap.get("row")).createCell(newSuperCodeLine);
		sheet.getRow((int)rootMap.get("row")).getCell(newSuperCodeLine).setCellValue(superOrgaCode2);
		
		
		List<Map<String, Object>> childList = queryOrgainfoBySuperCodeFromExcel((String)rootMap.get("old_orgacode"));
		for (int i = 1; i <= childList.size(); i++) {
			String temp = ""+i;
			String old_rootId3 = (String)childList.get(i-1).get("old_orgaid");
			String newOrgaId3 = newOrgaId2+"0000".substring(0,4-temp.length())+temp;
			String superOrgaCode3 = newOrgaCode2;
			System.out.println("temp:"+temp+"; old_rootId3"+old_rootId3+"; newOrgaCode2:"+newOrgaCode2);
			String newOrgaCode3 = newOrgaCode2+"000".substring(0,3-temp.length())+temp;
			initOrgaInfo(old_rootId3, newOrgaId3, newOrgaCode3, superOrgaCode3,childList.get(i-1));
		}
		
	}

	private static List<Map<String, Object>> queryOrgainfoBySuperCodeFromExcel(
			String old_rootId2) {
		List<Map<String,Object>> orgaList = new ArrayList<Map<String,Object>>();
		for(int i=1;i<sheet.getLastRowNum()+1;i++){
			Map<String, Object> orgaMap = queryOrgaInfoByRownum(i);
			String old_superCode_temp = (String)orgaMap.get("old_superorgacode");
			if (old_superCode_temp.equals(old_rootId2)) {
				orgaList.add(orgaMap);
			}
		}
		return orgaList;
	}

	private static void editInsertSql(String old_rootId2, String newOrgaId2,
			String newOrgaCode2, String superOrgaCode2,
			Map<String, Object> rootMap) {
		String orgaId = newOrgaId2;
		String orgaName = (String)rootMap.get("old_organame");
		String orgaTypeId = (newOrgaId2.length()/4*10>50?50:newOrgaId2.length()/4*10)+"";
		String orgaState = "0";
		String orgaCode = newOrgaCode2;
		String superOrgaCode = superOrgaCode2;
		String orgaDesc = rootMap.get("old_orgadesc")==null?"":(String)rootMap.get("old_orgadesc");
		String monitorId = rootMap.get("monitorId")==null?"":(String)rootMap.get("monitorId");
		String channelId = "";
		String tenantId = "001";
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String insertTime = df.format(new Date());
		
		String orgaInfoSql = "INSERT INTO `t_ucp_orgainfo` "
				+"(`ORGAID`, `ORGANAME`, `ORGATYPEID`, `ORGASTATE`, `ORGACODE`, `SUPERORGACODE`, "
				+"`ORGADESC`, `MONITORID`, `CHANNEL_ID`, `TENANT_ID`, `INSERTTIME`) "
				+"VALUES"
				+"('"+orgaId+"','"+orgaName+"','"+orgaTypeId+"','"+orgaState+"','"+orgaCode+"','"+superOrgaCode+"',"
				+"'"+orgaDesc+"','"+monitorId+"','"+channelId+"','"+tenantId+"','"+insertTime+"');\n";
		try {
			orgaInfo.write(orgaInfoSql);
		} catch (IOException e) {
			System.out.println("插入语句报错："+orgaInfoSql);
		}
		
	}
}
